package com.wu.dms.dao.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.wu.dms.dao.entity.Singer;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

/**
 * @author Cousin
 */
@Mapper
public interface SingerMapper extends BaseMapper<Singer> {
    @Select("SELECT " +
            "SUM(IF(sex = 1, 1, 0)) AS males, " +
            "SUM(IF(sex = 0, 1, 0)) AS females, " +
            "SUM(IF(sex = 2, 1, 0)) AS band,"+
            "SUM(IF(sex = 3, 1, 0)) AS groupStar,"+
            "location, COUNT(*) AS locationCount " +
            "FROM singer " +
            "WHERE location IS NOT NULL " +
            "GROUP BY location")
    List<Map<String, Object>> selectAllStatistics();

}
